package c0907m.group2.aitsm.core.db;

import c0907m.group2.aitsm.core.entity.SubjectInfo;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Author: LongDH
 * Date: 4/27/11
 * Time: 2:53 PM
 * <p/>
 * Interface for selecting, inserting, updating from Subjects table.
 */
public class SubjectDb extends BaseDb {
    private String insert = "INSERT INTO subjects (name, description) VALUES (?, ?)";
    private String update = "UPDATE subjects SET name = ?, description = ?, active = ? WHERE id = ?";

    /**
     * Insert a Subject into database.
     *
     * @param subject row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void insert(SubjectInfo subject) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(insert);

            st.setString(1, subject.getName());
            st.setString(2, subject.getDescription());
            st.executeUpdate();
        } finally {
            close();
        }
    }

    /**
     * Update a Subject into database.
     *
     * @param subject row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void update(SubjectInfo subject) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(update);

            doUpdate(subject);
        } finally {
            close();
        }
    }

    /**
     * Update a list of Subjects into database.
     *
     * @param subjects list of row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void update(List<SubjectInfo> subjects) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(update);

            for (SubjectInfo course : subjects) {
                doUpdate(course);
            }
        } finally {
            close();
        }
    }

    /**
     * Get a Subject for the given ID.
     *
     * @param id the row's ID to select.
     * @return row's information found, or null if none.
     * @throws SQLException if a database access error occurs.
     */
    public SubjectInfo select(int id) throws SQLException {
        String select = "SELECT * FROM subjects WHERE id = " + id;
        try {
            connect();
            st = con.prepareStatement(select);
            rs = st.executeQuery();

            if (rs.next()) {
                return getResult();
            }

            return null;
        } finally {
            close();
        }
    }

    /**
     * Get a list of all Subjects.
     *
     * @return list of row's information found.
     * @throws SQLException if a database access error occurs.
     */
    public List<SubjectInfo> select() throws SQLException {
        return select("");
    }

    /**
     * Get a list of Subjects for the given name.
     *
     * @param name the row's name to search.
     * @return list of row's information found.
     * @throws SQLException if a database access error occurs.
     */
    public List<SubjectInfo> select(String name) throws SQLException {
        String select = "SELECT * FROM subjects WHERE name LIKE '%" + name + "%'";
        try {
            connect();
            st = con.prepareStatement(select);
            rs = st.executeQuery();

            List<SubjectInfo> list = new ArrayList<SubjectInfo>();

            while (rs.next()) {
                list.add(getResult());
            }

            return list;
        } finally {
            close();
        }
    }

    /**
     * Update a data object into database.
     *
     * @param subject the data object.
     * @throws SQLException if a database access error occurs.
     */
    private void doUpdate(SubjectInfo subject) throws SQLException {
        st.setString(1, subject.getName());
        st.setString(2, subject.getDescription());
        st.setBoolean(3, subject.isActive());
        st.setInt(4, subject.getId());
        st.executeUpdate();
    }

    /**
     * Build a data object from ResultSet.
     *
     * @return the data object.
     * @throws SQLException if a database access error occurs.
     */
    private SubjectInfo getResult() throws SQLException {
        SubjectInfo course = new SubjectInfo();
        course.setId(rs.getInt(1));
        course.setName(rs.getString(2));
        course.setDescription(rs.getString(3));
        course.setActive(rs.getBoolean(4));

        return course;
    }
}
